﻿using System;
using System.Collections.Generic;
using System.Linq;
using System.Text;
using System.Data;
using System.Data.Sql;
using System.Data.SqlClient;



namespace Projekt
{
    /// <summary>
    /// Adatbazis muveletek
    /// </summary>
    sealed class Dalgen
    {
        private static string connectionString = @"Server=(local)\sqlexpress;Data Source=REB-PC;Initial Catalog=Projekt;Integrated Security=True";
        private static SqlConnection myConnection;
        private static Boolean opened=false;
        private static Dalgen instance=null;
        static readonly object padlock = new object();
        Dalgen()
        {
            try
            {
                myConnection = new SqlConnection(connectionString);
                myConnection.Open();
                myConnection.Close();
            }
            catch (Exception es) { throw (es); }
        }

        /// <summary>
        /// Megnezi, hogy letezik e mar peldany belole es ha letezik, visszakuld egy referenciat ra, kulonben letrehoz egyet
        /// </summary>   
        public static Dalgen Instance
        {
            get
            {
                lock (padlock)
                {
                    if (instance == null)
                    {
                        instance = new Dalgen();
                    }
                    return instance;
                }
            }
        }

        /// <summary>
        /// Megnyitja a kapcsolatot az adatbazissal
        /// </summary>
        /// <returns>true ha sikerult, kulonben false</returns>
        public Boolean OpenConnection()
        {
            if (!opened)
            {
                try
                {
                    myConnection.Open();
                    opened = true;
                    return true;
                }
                catch (Exception es) { throw (es);}
            }
            else { return true; }
           
        }

        /// <summary>
        /// Lezarja a kapcsolatot az adatbazissal
        /// </summary>
        /// <returns>true ha sikerult, kulonben false</returns>
        public Boolean CloseConnection()
        {
            if (opened)
            {
                try
                {
                    myConnection.Close();
                    opened = false;
                    return true;
                }
                catch (Exception es) { throw (es); }
            }
            else { return true; }
        }

        /// <summary>
        /// Kerest vegez az adatbazishoz(nem var valaszt)
        /// </summary>
        /// <param name="sqlCmd">sql parancs amit el szeretnenk vegezni</param>
        /// <returns>true ha sikerult, kulonben false</returns>
        public bool nonQueryCmd(String sqlCmd)
        {
            try
            {
                this.OpenConnection();
                SqlCommand MyCmd = new SqlCommand(sqlCmd, myConnection);
                if (MyCmd.ExecuteNonQuery() != 0) return true;
                else return false;
            }
            catch (Exception es) { throw (es); }
            finally { this.CloseConnection(); }
        }


        /// <summary>
        /// Kerest vegez az adatbazishoz(DataTable-t var valasznakk)
        /// </summary>
        /// <param name="sqlCmd">sql parancs amit el szeretnenk vegezni</param>
        /// <returns>az sql parancs eredmenye</returns>
        public DataTable QueryCmd(String sqlCmd)
        {
            try
            {
                this.OpenConnection();
                SqlCommand MyCmd = new SqlCommand(sqlCmd, myConnection);
                SqlDataReader dr = MyCmd.ExecuteReader();
                DataTable dt = new DataTable();
                dt.Load(dr);
                return dt;
            }
            catch (Exception es) { throw (es); }
            finally { this.CloseConnection(); }
        }


        /// <summary>
        /// Kerest vegez az adatbazishoz(1 db obiektumot var valasznak)
        /// </summary>
        /// <param name="sqlCmd">sql parancs amit el szeretnenk vegezni</param>
        /// <returns>az sql parancs eredmenye</returns>
        public Object ScalarCmd(String sqlCmd)
        {
            try
            {
                this.OpenConnection();
                SqlCommand MyCmd = new SqlCommand(sqlCmd, myConnection);
                Object result = MyCmd.ExecuteScalar();
                return result;
            }
            catch (Exception es) { throw (es); }
            finally { this.CloseConnection(); }
        }

        /// <summary>
        /// Meghivja a deleteNir tarolt eljarast
        /// </summary>
        /// <param name="nirNr">Nir sorszama amire meg szeretnenk hivni</param>
        public void deleteNir(String nirNr)
        {
            try
            {
                this.OpenConnection();
                SqlCommand cmd = new SqlCommand("deleteNir", myConnection);
                cmd.CommandType = CommandType.StoredProcedure;
                cmd.Parameters.Add(new SqlParameter("@nirNr", nirNr));
                cmd.ExecuteNonQuery();
            }
            catch (Exception es) { }
            finally { this.CloseConnection(); }
        }

        /// <summary>
        /// Meghivja a deleteOldNir tarolt eljarast(torli az adott datumnal regebbi NIR-eket)
        /// </summary>
        /// <param name="year">Ev</param>
        /// <param name="month">Honap</param>
        /// <param name="day">nap</param>
        public void deleteOldNir(String year, String month, String day)
        {
            try
            {
                this.OpenConnection();
                SqlCommand cmd = new SqlCommand("torolRegebbi", myConnection);
                cmd.CommandType = CommandType.StoredProcedure;
                String datum = year + "-" + month + "-" + day;  
                cmd.Parameters.Add(new SqlParameter("@Datum", datum));
                cmd.ExecuteNonQuery();
            }
            catch (Exception es) { }
            finally { this.CloseConnection(); }
        }

        /// <summary>
        /// Meghivja a vasarolCegtolStat tarolt eljarast(egy adott cegtol egy adott honapban menyit vasaroltak)
        /// </summary>
        /// <param name="year">Ev</param>
        /// <param name="month">Honap</param>
        /// <param name="CegID">Ceg azonosotioja</param>
        /// <returns>a tarolt eljaras eredmenye</returns>
        public Double vasarolCegtolStat(String year, String month, String CegID)
        {
            try
            {
                this.OpenConnection();
                SqlCommand cmd = new SqlCommand("vasarolCegtolStat", myConnection);
                cmd.CommandType = CommandType.StoredProcedure;
                cmd.Parameters.Add(new SqlParameter("@Ev", year));
                cmd.Parameters.Add(new SqlParameter("@Honap", month));
                cmd.Parameters.Add(new SqlParameter("@CegID", CegID));
                SqlParameter osszeg = cmd.Parameters.Add("@szum", SqlDbType.Float);
                osszeg.Direction = ParameterDirection.Output;
                cmd.ExecuteNonQuery();
                return Convert.ToSingle(osszeg.Value);
            }
            catch (Exception es) { return 0; }
            finally { 
                this.CloseConnection();
            }
        }

        /// <summary>
        /// Mehivja a vasarol1HonapbaStat tarotl eljarast(egy adott honapban menyit vasaroltak)
        /// </summary>
        /// <param name="year">ev</param>
        /// <param name="month">honap</param>
        /// <returns>az tarolt eljaras eredmenye</returns>
        public double vasarol1HonapbaStat(String year, String month)
        {
            try
            {
                this.OpenConnection();
                SqlCommand cmd = new SqlCommand("vasarol1HonapbaStat", myConnection);
                cmd.CommandType = CommandType.StoredProcedure;
                cmd.Parameters.Add(new SqlParameter("@Ev", year));
                cmd.Parameters.Add(new SqlParameter("@Honap", month));
                SqlParameter osszeg = cmd.Parameters.Add("@szum", SqlDbType.Float);
                osszeg.Direction = ParameterDirection.Output;
                cmd.ExecuteNonQuery();
                return Convert.ToSingle(osszeg.Value);
            }
            catch (Exception es) { return 0; }
            finally
            {
                this.CloseConnection();
            }
        }
    }
}
